USE [GLDW]
GO
/****** Object:  UserDefinedFunction [dbo].[LDW_BI_STRING_TO_TABLE_FUNC]    Script Date: 10/28/2013 13:26:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
  ***************************************************************************
  Create Date  : 2013. 10. 28
  Initionalize : JUN,YOUNG-WOOK
  Func Name    : [LDW_BI_STRING_TO_TABLE_FUNC]
  Desc         : STRING을 TABLE로, STRING을 @로 붙여서~
  USE          : 
				SELECT * FROM LDW_MST_SUBSIDIARY_REGION_MAPPING
				WHERE SUBSIDIARY_NAME IN (SELECT VALUE FROM LDW_BI_STRING_TO_TABLE_FUNC('LGEKR@LGEAP'))
  SOURCE FROM  : http://stackoverflow.com/questions/1525126/sql-in-clause-in-storedprocedurehow-to-pass-values
				 http://mitchelsellers.com/blogs/2008/08/08/using-the-in-clause-with-stored-procedures.aspx
  ***************************************************************************
  */
ALTER FUNCTION [dbo].[LDW_BI_STRING_TO_TABLE_FUNC](@list ntext)
      RETURNS @tbl TABLE (Position INT IDENTITY(1, 1) NOT NULL,
                          Value VARCHAR(100) NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      nvarchar(4000),
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex('@', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (Value) VALUES(@str)
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
            SET @pos = charindex('@', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (Value) VALUES(@leftover)

      RETURN
   END   





